package com.b2c.repository.erp;

import com.b2c.entity.result.PagingResponse;
import com.b2c.entity.ErpGoodsStockInfoEntity;
import com.b2c.entity.erp.ErpGoodsStockInfoItemEntity;
import com.b2c.entity.erp.InvoiceEntity;
import com.b2c.entity.erp.InvoiceInfoEntity;
import com.b2c.entity.erp.enums.*;
import com.b2c.entity.erp.vo.ErpInvoiceInfoVo;
import com.b2c.entity.erp.vo.ErpInvoiceVo;
import com.b2c.entity.erp.vo.InvoiceInfoListVo;
import com.b2c.entity.result.EnumResultVo;
import com.b2c.entity.result.ResultVo;
import com.b2c.entity.vo.wms.ErpPurchaseGoodVo;
import com.b2c.entity.vo.wms.InvoiceDetailVo;
import com.b2c.entity.enums.IsDeleteEnum;
import com.b2c.repository.Tables;
import com.b2c.repository.utils.OrderNumberUtils;
import com.b2c.entity.enums.erp.EnumGoodsStockLogSourceType;
import com.b2c.entity.enums.erp.EnumGoodsStockLogType;

import com.b2c.entity.vo.finance.ErpStockInFormItemVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.StringUtils;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 描述：
 * 购货销货表单Repository
 *
 * @author qlp
 * @date 2019-03-22 09:43
 */
@Repository
public class InvoiceRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Autowired
    private ExpressRepository expressRepository;
    @Autowired
    DataSourceTransactionManager dataSourceTransactionManager;
    @Autowired
    TransactionDefinition transactionDefinition;

    /**
     * 采购表单提交
     *
     * @param invoiceForm
     * @param invoiceInfo
     * @return
     */
    @Transactional
    public Integer purchaseAddSubmit(InvoiceEntity invoiceForm, List<InvoiceInfoEntity> invoiceInfo) {
        //新增表单invoice
        String sql = "INSERT INTO " + Tables.ErpInvoice + 
        " (contactId,billNo,billDate,userId,userName,transType,transTypeName,totalAmount,disRate,disAmount,amount,totalDiscount,totalQuantity,inQuantity,rpAmount,arrears,description,billType,billStatus,isDelete,createTime,contractNo,srcOrderId,freight) "
        +"VALUE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setLong(1, invoiceForm.getContactId());
                ps.setString(2, invoiceForm.getBillNo());
                ps.setString(3, invoiceForm.getBillDate());
                ps.setInt(4, invoiceForm.getUserId());
                ps.setString(5, invoiceForm.getUserName());
                ps.setString(6, invoiceForm.getTransType());
                ps.setString(7, invoiceForm.getTransTypeName());
                ps.setDouble(8, invoiceForm.getTotalAmount());
                ps.setDouble(9, invoiceForm.getDisRate());
                ps.setDouble(10, invoiceForm.getDisAmount());
                ps.setDouble(11, invoiceForm.getAmount());
                ps.setDouble(12, invoiceForm.getTotalDiscount());
                ps.setLong(13, invoiceForm.getTotalQuantity());
                ps.setLong(14, 0);
                ps.setDouble(15, invoiceForm.getRpAmount());
                ps.setDouble(16, invoiceForm.getArrears());
                ps.setString(17, invoiceForm.getDescription());
                ps.setString(18, invoiceForm.getBillType());
                ps.setInt(19, invoiceForm.getBillStatus());
                ps.setInt(20, invoiceForm.getIsDelete());
                ps.setLong(21, System.currentTimeMillis() / 1000);
                ps.setString(22, invoiceForm.getContractNo());
                ps.setString(23, invoiceForm.getSrcOrderId());
                ps.setDouble(24, invoiceForm.getFreight()==null?0:invoiceForm.getFreight());
                return ps;
            }
        }, keyHolder);

        Integer invoiceId = keyHolder.getKey().intValue();

        //循环处理
        for (InvoiceInfoEntity info : invoiceInfo) {

            BigDecimal price=jdbcTemplate.queryForObject("SELECT IFNULL((SELECT i.purPrice  from erp_goods_stock_info_item i LEFT JOIN erp_goods_stock_info e ON e.id=i.stockInfoId WHERE  e.specId=? ORDER BY i.id DESC LIMIT 1),0) price ",BigDecimal.class,info.getSpecId());

            /***1、新增invoice_info***/
            String iSql = "INSERT INTO " + Tables.ErpInvoiceInfo + 
            " (iid,billNo,transType,transTypeName,amount,billDate,serialno,description,goodsId,goodsNumber,specId,specNumber,price,disAmount,disRate,quantity,inQuantity,locationId,isDelete,status,srcOrderNo) "
            +"VALUE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            jdbcTemplate.update(iSql,
                    invoiceId,
                    invoiceForm.getBillNo(),
                    invoiceForm.getTransType(),
                    invoiceForm.getTransTypeName(),
                    info.getAmount(),
                    invoiceForm.getBillDate(),
                    info.getSerialno(),
                    info.getDescription(),
                    info.getGoodsId(),
                    info.getGoodsNumber(),
                    info.getSpecId(),
                    info.getSpecNumber(),
                    info.getPrice(),
                    info.getDisAmount(),
                    info.getDisRate(),
                    info.getQuantity(),
                    0,
                    info.getLocationId(),
                    IsDeleteEnum.Normal.getIndex(),
                    InvoiceInfoStatusEnum.Wait.getIndex(),
                    info.getSrcOrderNo());

                    /**更新发货订单明细状态 */

            /***2、更新商品库存erp_goods_spec***/
//            jdbcTemplate.update("UPDATE " + Tables.ErpGoodsSpec + " SET currentQty=currentQty+? WHERE goodsId=? AND specNumber=?", info.getQuantity(), info.getGoodsId(), info.getSpecNumber());

            /***3、更新商品库存erp_goods_stock_info***/
            //ErpGoodsStockInfo
//            int r = jdbcTemplate.update("UPDATE " + Tables.ErpGoodsStockInfo + " SET currentQty=currentQty+? WHERE goodsId=? AND specNumber=? AND locationId=?", info.getQuantity(), info.getGoodsId(), info.getSpecNumber(), info.getLocationId());
//            if (r < 1) {
//                //没有更新到，新增一条数据
//                jdbcTemplate.update("INSERT INTO " + Tables.ErpGoodsStockInfo + " (goodsId,specNumber,locationId,currentQty) VALUE (?,?,?,?)", info.getGoodsId(), info.getSpecNumber(), info.getLocationId(), info.getQuantity());
//            }


        }

        if(StringUtils.hasText(invoiceForm.getSrcOrderId())&& invoiceForm.getTransType().equals(InvoiceTransTypeEnum.OrderDaiFa.getIndex())){
            //更新订单状态
            String s = "UPDATE erp_order_send SET isSettle=1 WHERE id in ("+invoiceForm.getSrcOrderId()+")";
            jdbcTemplate.update(s);
        }
        if(StringUtils.hasText(invoiceForm.getSrcOrderId())&& invoiceForm.getTransType().equals(InvoiceTransTypeEnum.DaiFaRefund.getIndex())){
            //更新订单状态,EnumOrderReturnStatus 4 退回供应商
            String s = "UPDATE erp_order_send_return SET isSettle=1,`status`=4 WHERE id in ("+invoiceForm.getSrcOrderId()+")";
            jdbcTemplate.update(s);
        }
        return 1;
    }

    /**
     * 销货单
     *
     * @param invoiceForm
     * @param invoiceInfo
     * @return
     */
//     @Transactional
//     public Integer saleFormSubmit(InvoiceEntity invoiceForm, List<InvoiceInfoEntity> invoiceInfo) {
//         //新增表单invoice
//         String sql = "INSERT INTO " + Tables.ErpInvoice + " (contactId,billNo,billDate,userId,userName,transType,transTypeName,totalAmount,disRate,disAmount,amount,totalDiscount,totalQuantity,inQuantity,rpAmount,arrears,description,billType,billStatus,isDelete,createTime,srcOrderNo,srcOrderId,payment,hxAmount,hxStateCode,salesId) VALUE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
//         KeyHolder keyHolder = new GeneratedKeyHolder();
//         jdbcTemplate.update(new PreparedStatementCreator() {
//             @Override
//             public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
//                 PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
//                 ps.setLong(1, invoiceForm.getContactId());
//                 ps.setString(2, invoiceForm.getBillNo());
//                 ps.setString(3, invoiceForm.getBillDate());
//                 ps.setInt(4, invoiceForm.getUserId());
//                 ps.setString(5, invoiceForm.getUserName());
//                 ps.setString(6, invoiceForm.getTransType());
//                 ps.setString(7, invoiceForm.getTransTypeName());
//                 ps.setDouble(8, invoiceForm.getTotalAmount());
//                 ps.setDouble(9, invoiceForm.getDisRate());
//                 ps.setDouble(10, invoiceForm.getDisAmount());
//                 ps.setDouble(11, invoiceForm.getAmount());
//                 ps.setDouble(12, invoiceForm.getTotalDiscount());
//                 ps.setLong(13, invoiceForm.getTotalQuantity());
//                 ps.setLong(14, invoiceForm.getInQuantity() == null ? 0 : invoiceForm.getInQuantity());
//                 ps.setDouble(15, invoiceForm.getRpAmount());
//                 ps.setDouble(16, invoiceForm.getArrears());
//                 ps.setString(17, invoiceForm.getDescription());
//                 ps.setString(18, invoiceForm.getBillType());
//                 ps.setInt(19, invoiceForm.getBillStatus());
//                 ps.setInt(20, invoiceForm.getIsDelete());
//                 ps.setLong(21, System.currentTimeMillis() / 1000);
//                 ps.setString(22, invoiceForm.getSrcOrderNo());
//                 ps.setLong(23, invoiceForm.getSrcOrderId());
//                 ps.setDouble(24, invoiceForm.getPayment() == null ? 0.0 : invoiceForm.getPayment());
//                 ps.setDouble(25, invoiceForm.getHxAmount() == null ? 0.0 : invoiceForm.getHxAmount());
//                 ps.setInt(26, invoiceForm.getHxStateCode() == null ? 0 : invoiceForm.getHxStateCode());
//                 ps.setInt(27, invoiceForm.getSalesId() == null ? 0 : invoiceForm.getSalesId());
//                 return ps;
//             }
//         }, keyHolder);

//         Integer invoiceId = keyHolder.getKey().intValue();

//         //循环处理
//         for (InvoiceInfoEntity info : invoiceInfo) {
//             /***1、新增invoice_info***/
//             String iSql = "INSERT INTO " + Tables.ErpInvoiceInfo + " (iid,billNo,transType,transTypeName,amount,billDate,serialno,description,goodsId,goodsNumber,specId,specNumber,price,disAmount,disRate,quantity,inQuantity,srcOrderNo,locationId,isDelete,status) VALUE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
//             jdbcTemplate.update(iSql, invoiceId,
//                     invoiceForm.getBillNo(),
//                     invoiceForm.getTransType(),
//                     invoiceForm.getTransTypeName(),
//                     info.getAmount(),
//                     invoiceForm.getBillDate(),
//                     info.getSerialno(),
//                     info.getDescription(),
//                     info.getGoodsId(),
//                     info.getGoodsNumber(),
//                     info.getSpecId(),
//                     info.getSpecNumber(),
//                     info.getPrice(),
//                     info.getDisAmount(),
//                     info.getDisRate(),
//                     info.getQuantity(),
//                     info.getInQuantity(),
//                     info.getSrcOrderNo(),
//                     info.getLocationId(),
//                     IsDeleteEnum.Normal.getIndex(),
//                     info.getStatus()
//             );

// //            /***2、更新商品库存erp_goods_spec***/
// //            jdbcTemplate.update("UPDATE " + Tables.ErpGoodsSpec + " SET currentQty=currentQty-? WHERE goodsId=? AND specNumber=?", info.getQuantity(), info.getGoodsId(), info.getSpecNumber());
// //
// //            /***3、更新商品库存erp_goods_stock_info***/
// //            int r = jdbcTemplate.update("UPDATE " + Tables.ErpGoodsStockInfo + " SET currentQty=currentQty-? WHERE goodsId=? AND specNumber=? AND locationId=?", info.getQuantity(), info.getGoodsId(), info.getSpecNumber(), info.getLocationId());

//         }
//         return 1;
//     }

    /**
     * 获取采购分页列表
     *
     * @param pageIndex
     * @param pageSize
     * @param transType 交易类型
     * @param billNo
     * @param startDate
     * @param endDate
     * @return
     */
    public PagingResponse<InvoiceEntity> getList(Integer pageIndex, Integer pageSize, InvoiceTransTypeEnum transType, InvoiceBillTypeEnum billTypeEnum, InvoiceBillStatusEnum billStatusEnum, InvoiceCheckoutStatusEnum checkoutStatusEnum,
                                                 InvoiceQualifiedStatusEnum qualifiedStatusEnum, String billNo, String startDate, String endDate, Integer contactId) {
        String sql = "SELECT SQL_CALC_FOUND_ROWS i.*,s.name as contactName FROM " + Tables.ErpInvoice + " as i LEFT JOIN " + Tables.ErpContact + " as s on s.id=i.contactId WHERE i.isDelete=0  ";

        List<Object> params = new ArrayList<>();
        if(transType == null){
            sql += " AND (i.transType=? or i.transType=?)";
            params.add(InvoiceTransTypeEnum.Purchase.getIndex());
            params.add(InvoiceTransTypeEnum.OrderDaiFa.getIndex());
        }else{
            sql += " AND i.transType=? ";
            params.add(transType.getIndex());
        }
        if(contactId!=null && contactId>0){
            sql += " AND i.contactId=? ";
            params.add(contactId);
        }
      
        if (billTypeEnum != null) {
            sql += " AND i.billType=? ";
            params.add(billTypeEnum.getIndex());
        }
        if (billStatusEnum == InvoiceBillStatusEnum.Picked) {
            sql += " AND (i.billStatus=? OR i.billStatus=? )";
            params.add(InvoiceBillStatusEnum.Picked.getIndex());
            params.add(InvoiceBillStatusEnum.LogisticsPrinted.getIndex());
        } else if (billStatusEnum != null) {
            sql += " AND i.billStatus=? ";
            params.add(billStatusEnum.getIndex());
        }
        if (checkoutStatusEnum != null) {
            sql += " AND i.checkoutStatus=? ";
            params.add(checkoutStatusEnum.getIndex());
        }
        if (qualifiedStatusEnum != null) {
            sql += " AND i.qualifiedStatus=? ";
            params.add(qualifiedStatusEnum.getIndex());
        }

        if (StringUtils.isEmpty(billNo) == false) {
            sql += " AND billNo=?";
            params.add(billNo);
        } else {
            if (StringUtils.isEmpty(startDate) == false) {
                sql += " AND billDate >= ?";
                params.add(startDate);

            }
            if (StringUtils.isEmpty(endDate) == false) {
                sql += " AND billDate <= ?";
                params.add(endDate);
            }
        }

        sql += "ORDER BY i.id DESC LIMIT ?,?";
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        List<InvoiceEntity> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(InvoiceEntity.class), params.toArray(new Object[params.size()]));
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }

    /**
     * 查询总页数
     *
     * @return
     */
    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    public Integer deleteForm(Long id) {
        return jdbcTemplate.update("UPDATE " + Tables.ErpInvoice + "SET isDelete=? WHERE id=?", IsDeleteEnum.Deleted.getIndex(), id);
    }


    /***
     * 获取采购表单详情
     * @param id
     * @return
     */
    public InvoiceDetailVo getInvoiceDetail(Long id) {
        var detail = new InvoiceDetailVo();
        String sql = "SELECT inv.*,c.name as contactName FROM " + Tables.ErpInvoice + " inv " +
                " left join " + Tables.ErpContact + " c on c.id=inv.contactId " +
                " WHERE inv.id=?";
        var entity = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(InvoiceDetailVo.class), id);
        if (entity != null && entity.size() > 0) {
            detail = entity.get(0);
            String itemSQL = "SELECT ii.id,ii.iid,ii.price,ii.amount,ii.quantity,ii.inQuantity,ii.qualifiedQuantity,ii.abnormalQuantity,ii.status" +
                    ",g.id as goodsId,g.`name` as goodsName,g.number as goodsNumber,unit.name as unitName" +
                     ",gs.id as specId,gs.`specNumber`,gs.color_value,gs.size_value,gs.style_value,gs.color_image,ii.description"+
                    ",ii.`locationId`,sh.name AS locationName "+
                    " FROM " + Tables.ErpInvoiceInfo + " ii " +
                    " LEFT JOIN " + Tables.ErpGoods + " g on g.id=ii.goodsId " +
                    " LEFT JOIN " + Tables.ErpGoodsSpec + " gs on gs.id=ii.specId " +
                    " LEFT JOIN " + Tables.ErpStockLocation + " as sh on sh.id=ii.`locationId` " +
                    " LEFT JOIN " + Tables.ErpUnit + " unit on unit.id=g.unitId " +
                    " WHERE ii.iid=?";


            detail.setItems(jdbcTemplate.query(itemSQL, new BeanPropertyRowMapper<>(InvoiceInfoListVo.class), id));
            String stockSQL = "SELECT *,IFNULL((select number as locationId from erp_stock_location where id=A.locationId ),0) locationName FROM erp_goods_stock_info A WHERE A.specId=? and A.goodsId=? AND isDelete=0";
            detail.getItems().forEach(i->i.setStocks(jdbcTemplate.query(stockSQL, new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class),i.getSpecId(),i.getGoodsId())));
            return detail;
        }
        return null;
    }

    /***
     * 获取采购表单详情
     * @param billNo 采购单号
     * @return
     */
    public InvoiceDetailVo getInvoiceDetail(String billNo) {
        var detail = new InvoiceDetailVo();
        String sql = "SELECT inv.*,c.name as contactName FROM " + Tables.ErpInvoice + " inv " +
                " left join " + Tables.ErpContact + " c on c.id=inv.contactId " +
                " WHERE inv.billNo=?";
        //查询采购单
        var entity = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(InvoiceDetailVo.class), billNo);


        if (entity != null && entity.size() > 0) {
            detail = entity.get(0);
            String itemSQL = "SELECT ii.id,ii.iid,ii.price,ii.amount,ii.quantity,ii.inQuantity,ii.qualifiedQuantity,ii.abnormalQuantity,ii.status" +
                    ",g.id as goodsId,g.`name` as goodsName,g.number as goodsNumber" +
                    ",gs.id as specId,gs.`specNumber`,gs.color_value,gs.size_value,gs.style_value"+
                    ",ii.`locationId`,sh.name AS locationName "+
                    " FROM " + Tables.ErpInvoiceInfo + " ii " +
                    " LEFT JOIN " + Tables.ErpGoods + " g on g.id=ii.goodsId " +
                    " LEFT JOIN " + Tables.ErpGoodsSpec + " gs on gs.id=ii.specId " +
                    " LEFT JOIN " + Tables.ErpStockLocation + " as sh on sh.id=ii.`locationId` " +

                    " WHERE ii.iid=?";


            detail.setItems(jdbcTemplate.query(itemSQL, new BeanPropertyRowMapper<>(InvoiceInfoListVo.class), entity.get(0).getId()));
            return detail;
        }
        return null;
    }

    /***
     * 获取表单信息
     * @param id
     * @return
     */
    public InvoiceEntity getInvoiceById(Long id) {
        String sql = "SELECT * FROM " + Tables.ErpInvoice + " WHERE id=?";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(InvoiceEntity.class), id);
        } catch (Exception e) {
            return null;
        }
    }

    /***
     * 获取表单信息
     * @param billNo
     * @return
     */
    public InvoiceEntity getInvoiceByBillNo(String billNo) {
        String sql = "SELECT * FROM " + Tables.ErpInvoice + " WHERE billNo=?";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(InvoiceEntity.class), billNo);
        } catch (Exception e) {
            return null;
        }
    }

    /**
     * 根据规格id和采购单id查询商品信息
     *
     * @param invoiceId
     * @param specNumber 商品规格编码
     * @return
     */
    public InvoiceInfoListVo getGoodsSpecByInvoiceAndSpec(Long invoiceId, String specNumber) {
        String itemSQL = "SELECT ii.id,ii.goodsId,g.`name` as goodsName,g.number as goodsNumber,ii.specId,ii.specNumber" +
                ",gs.color_value,gs.size_value,gs.style_value"+
                ",g.unitName,ii.price,ii.amount,ii.quantity,ii.inQuantity,ii.qualifiedQuantity,gs.locationId FROM "
                + Tables.ErpInvoiceInfo + " ii " +
                " LEFT JOIN " + Tables.ErpGoods + " g on g.id=ii.goodsId " +
                " LEFT JOIN " + Tables.ErpGoodsSpec + " gs on gs.id=ii.specId " +
                " WHERE ii.iid=? and ii.specNumber=? LIMIT 1";
        try {
            return jdbcTemplate.queryForObject(itemSQL, new BeanPropertyRowMapper<>(InvoiceInfoListVo.class), invoiceId, specNumber);
        } catch (Exception e) {
            return null;
        }
    }






    /**
     * 订单发货
     *
     * @param invoiceId
     * @return
     */
//    @Transactional
//    public ResultVo<Integer> invoiceGoodsSend(Long invoiceId) {
//
//        //手动开启事务
//        TransactionStatus transactionStatus = dataSourceTransactionManager.getTransaction(transactionDefinition);
//
//
//        /******0、查询表单*******/
//        InvoiceEntity invoice = null;
//        try {
//            invoice = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpInvoice + " WHERE id=? AND transType=? AND billType=?", new BeanPropertyRowMapper<>(InvoiceEntity.class), invoiceId, InvoiceTransTypeEnum.Order.getIndex(), InvoiceBillTypeEnum.ORDER.getIndex());
//            if (invoice.getBillStatus().intValue() == InvoiceBillStatusEnum.Picked.getIndex())
//                return new ResultVo<>(EnumResultVo.Fail, "请先打印快递单才能出库");
//            if (invoice.getBillStatus().intValue() == InvoiceBillStatusEnum.Out.getIndex())
//                return new ResultVo<>(EnumResultVo.Fail, "已经出过库了（已经发货）");
//
//        } catch (Exception e) {
//            //手动回滚事务
//            dataSourceTransactionManager.rollback(transactionStatus);//最好是放在catch 里面,防止程序异常而事务一直卡在哪里未提交
////            return 0;
//            return new ResultVo<>(EnumResultVo.Fail, "发货单数据不存在");
//        }
//        if (invoice == null) return new ResultVo<>(EnumResultVo.Fail, "发货单数据不存在");
//
//        OrdersEntity order = null;
//        try {
//            //查询订单
//            order = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.Order + " WHERE id=?", new BeanPropertyRowMapper<>(OrdersEntity.class), invoice.getSrcOrderId());
//            if (order.getState() != OrderStateEnums.WaitSend.getIndex()) {
//                //订单非支付状态
//                //                return -2;//
//                return new ResultVo<>(EnumResultVo.Fail, "订单状态不正确(非支付状态)");
//            }
//        } catch (Exception e) {
//            //手动回滚事务
//            dataSourceTransactionManager.rollback(transactionStatus);//最好是放在catch 里面,防止程序异常而事务一直卡在哪里未提交
////            return 0;
//            return new ResultVo<>(EnumResultVo.Fail, "订单数据不存在");
//        }
//
//        if (order == null) return new ResultVo<>(EnumResultVo.Fail, "订单数据不存在");
//
//        /********有赞订单发货***********/
//        if (order.getType() == 1) {
//            Integer yzId = jdbcTemplate.queryForObject("SELECT IFNULL((SELECT yz_id FROM express_company where code=? ),0)", Integer.class, order.getSendCompanyCode());
//            YouzanLogisticsOnlineConfirmResult result = YouzanClient.updOrderLogistics(order.getOrderNum(), yzId.toString(), order.getSendCode());
//            if (null == result || result.getCode() != 200 || result.getSuccess() == false)
//                return new ResultVo<>(EnumResultVo.Fail, "有赞发货异常," + result.getMessage());
//        }
//
//
//        try {
//            /********1、更新invoice状态(更新为已出库)***********/
//            jdbcTemplate.update("UPDATE " + Tables.ErpInvoice + " SET billStatus=? WHERE id=?", InvoiceBillStatusEnum.Out.getIndex(), invoiceId);
//
//            /********2、更新Orders状态***********/
//            String sql = "UPDATE " + Tables.Order + " SET send_company=?,send_company_code=?,send_code=?,send_time=?,state=? where id=?";
//            int r2 = jdbcTemplate.update(sql, invoice.getLogisticsCompany(), invoice.getLogisticsCompanyCode(), invoice.getLogisticsNumber(), System.currentTimeMillis() / 1000, OrderStateEnums.Delivered.getIndex(), order.getId());
//            if (r2 > 0) {
//                /**2.1、添加订单日志 order_logs**/
//                String logsSQL = "INSERT INTO " + Tables.OrderLogs + " (order_id,type,comment,create_on,create_by) VALUE (?,?,?,?,?)";
//                jdbcTemplate.update(logsSQL, order.getId(), 0, "订单发货", System.currentTimeMillis() / 1000, "system");
//
//                //2.2、新增订单物流信息order_logistics
//                String orderLogistics = "INSERT " + Tables.OrderLogistics + " (comment,order_id,create_on,create_by,state,type) VALUES (?,?,?,?,?,?)";
//                jdbcTemplate.update(orderLogistics, "订单已发货", order.getId(), System.currentTimeMillis() / 1000, "", 0, 0);
//            }
//            /********3、更新goods及goods_spec表锁定数据***********/
//            //查询订单明细
//            String itemSQL = "SELECT * FROM " + Tables.OrderItem + " WHERE order_id=?";
//            List<OrderItemEntity> items = jdbcTemplate.query(itemSQL, new BeanPropertyRowMapper<>(OrderItemEntity.class), order.getId());
//
//            /********4、更新库存信息***********/
//            //查询invoice_info
//            String infoSQL = "SELECT * FROM " + Tables.ErpInvoiceInfo + " WHERE iid=?";
//            List<InvoiceInfoEntity> infos = jdbcTemplate.query(infoSQL, new BeanPropertyRowMapper<>(InvoiceInfoEntity.class), invoiceId);
//            if (infos != null && infos.size() > 0) {
//                for (var info : infos) {
//                    var spec = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpGoodsSpec + "WHERE id=?", new BeanPropertyRowMapper<>(GoodsSpecEntity.class), info.getSpecId());
//                    //循环更新库存 erp_goods_spec
//                    jdbcTemplate.update("UPDATE " + Tables.ErpGoodsSpec + " SET currentQty=currentQty-? WHERE id=?", info.getQuantity(), spec.getId());
//
//                    //循环更新库存 erp_goods_stock_info
//                    jdbcTemplate.update("UPDATE " + Tables.ErpGoodsStockInfo + " SET currentQty=currentQty-? WHERE specId=? AND locationId=? ", info.getQuantity(), spec.getId(), spec.getLocationId());
//                }
//            }
//            //手动提交事务
//            dataSourceTransactionManager.commit(transactionStatus);//提交
//        } catch (Exception e) {
//            //手动回滚事务
//            dataSourceTransactionManager.rollback(transactionStatus);//最好是放在catch 里面,防止程序异常而事务一直卡在哪里未提交
//        }
//
//        return new ResultVo<>(EnumResultVo.SUCCESS);
//    }


    /**
     * 检验
     */
//    @Transactional
//    public void updataCheckout(ArrayList<Integer> id, ArrayList<Integer> quantity, ArrayList<String> abnormalQuantity, String userName, InvoiceCheckoutStatusEnum checkoutStatusEnum) {
//        var qualified = 0;
//        var iid = 0;
//        Date date = new Date();
//        for (int i = 0; i < id.size(); i++) {
//            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//            String time = dateFormat.format(date.getTime());
//            int kid = id.get(i);
//            int quan = quantity.get(i);//总数量
//            int abnor = Integer.parseInt(abnormalQuantity.get(i));//异常数量
//            int qualifiedQuantity = quan - abnor;//合格数量
//            qualified = qualified + qualifiedQuantity;
//            jdbcTemplate.update("UPDATE " + Tables.ErpInvoiceInfo + " SET qualifiedQuantity=?,abnormalQuantity=?,billTime=?,checkoutStatus=? WHERE id=? AND isDelete=0", qualifiedQuantity, abnor, time, InvoiceCheckoutStatusEnum.Abnormal.getIndex(), kid);
//            iid = jdbcTemplate.queryForObject("SELECT iid FROM " + Tables.ErpInvoiceInfo + " WHERE id = ? AND isDelete=0", Integer.class, kid);
//        }
//        if (qualified == 0) {
//            jdbcTemplate.update("UPDATE " + Tables.ErpInvoice + " SET qualifiedQuantity=?,checkoutStatus=?,checkoutName=?,checkoutTime=? WHERE id=? AND isDelete=0", qualified, checkoutStatusEnum.getIndex(), userName, new Date().getTime(), iid);
//        } else {
//            jdbcTemplate.update("UPDATE " + Tables.ErpInvoice + " SET qualifiedQuantity=?,checkoutStatus=?,checkoutName=?,checkoutTime=?,qualifiedStatus=? WHERE id=? AND isDelete=0", qualified, checkoutStatusEnum.getIndex(), userName, new Date().getTime(), InvoiceQualifiedStatusEnum.Qualified.getIndex(), iid);
//        }
//
//    }

    /**
     * 跳过检验
     */
//    @Transactional
//    public void updataCheckoutIt(Integer id, String userName, Integer stutas) {
//        Long totalQuantity = jdbcTemplate.queryForObject("select totalQuantity from " + Tables.ErpInvoice + " where id=?", Long.class, id);
//        jdbcTemplate.update("UPDATE " + Tables.ErpInvoice + " SET qualifiedQuantity=?,checkoutStatus=?,checkoutName=?,checkoutTime=?,qualifiedStatus=? WHERE id=? AND isDelete=0", totalQuantity, stutas, userName, new Date().getTime(), InvoiceQualifiedStatusEnum.Qualified.getIndex(), id);
//
//        List<InvoiceInfoEntity> query = jdbcTemplate.query("select * from " + Tables.ErpInvoiceInfo + " where iid=?", new BeanPropertyRowMapper<>(InvoiceInfoEntity.class), id);
//        for (InvoiceInfoEntity item:query){
//            jdbcTemplate.update("UPDATE " + Tables.ErpInvoiceInfo + " SET abnormalQuantity=?,qualifiedQuantity=?,billTime=? WHERE id=? AND isDelete=0 ", 0,item.getQuantity(), new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date().getTime()), item.getId());
//        }
//    }

    /**
     * 获取检验分页列表
     *
     * @param pageIndex
     * @param pageSize
     * @param transType 交易类型
     * @param billNo
     * @return
     */
//    public PagingResponse<InvoiceEntity> getCheckoutList(Integer pageIndex, Integer pageSize, InvoiceTransTypeEnum transType, InvoiceBillTypeEnum billTypeEnum, InvoiceBillStatusEnum billStatusEnum, String billNo) {
//        String sql = "SELECT SQL_CALC_FOUND_ROWS i.*,s.name as contactName FROM " + Tables.ErpInvoice + " as i LEFT JOIN " + Tables.ErpContact + " as s on s.id=i.contactId WHERE i.isDelete=0 AND i.checkoutStatus=0 AND i.transType=? ";
//
//        List<Object> params = new ArrayList<>();
//        params.add(transType.getIndex());
//        if (billTypeEnum != null) {
//            sql += " AND i.billType=? ";
//            params.add(billTypeEnum.getIndex());
//        }
//        if (billStatusEnum != null) {
//            sql += " AND i.billStatus=? ";
//            params.add(billStatusEnum.getIndex());
//        }
//
//        if (StringUtils.isEmpty(billNo) == false) {
//            sql += " AND billNo=? ";
//            params.add(billNo);
//        }
//
//        sql += "ORDER BY i.id DESC LIMIT ?,?";
//        params.add((pageIndex - 1) * pageSize);
//        params.add(pageSize);
//        List<InvoiceEntity> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(InvoiceEntity.class), params.toArray(new Object[params.size()]));
//        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
//    }

    /**
     * 获取已检验分页列表
     *
     * @param pageIndex
     * @param pageSize
     * @param transType 交易类型
     * @param billNo
     * @return
     */
//    public PagingResponse<InvoiceEntity> getCheckoutOk(Integer pageIndex, Integer pageSize, InvoiceTransTypeEnum transType, InvoiceBillTypeEnum billTypeEnum, InvoiceBillStatusEnum billStatusEnum, InvoiceCheckoutStatusEnum checkoutStatusEnum, String billNo) {
//        String sql = "SELECT SQL_CALC_FOUND_ROWS i.*,s.name as contactName FROM " + Tables.ErpInvoice + " as i LEFT JOIN " + Tables.ErpContact + " as s on s.id=i.contactId WHERE i.isDelete=0  AND i.transType=? ";
//
//        List<Object> params = new ArrayList<>();
//        params.add(transType.getIndex());
//        if (billTypeEnum != null) {
//            sql += " AND i.billType=? ";
//            params.add(billTypeEnum.getIndex());
//        }
//        if (billStatusEnum != null) {
//            sql += " AND i.billStatus=? ";
//            params.add(billStatusEnum.getIndex());
//        }
//        if (checkoutStatusEnum != null) {
//            sql += " AND i.checkoutStatus=? ";
//            params.add(checkoutStatusEnum.getIndex());
//        }
//
//        if (StringUtils.isEmpty(billNo) == false) {
//            sql += " AND billNo=? ";
//            params.add(billNo);
//        }
//
//
//        sql += "ORDER BY i.id DESC LIMIT ?,?";
//        params.add((pageIndex - 1) * pageSize);
//        params.add(pageSize);
//        List<InvoiceEntity> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(InvoiceEntity.class), params.toArray(new Object[params.size()]));
//        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
//    }

    /**
     * 获取异常分页列表
     *
     * @param pageIndex
     * @param pageSize
     * @param billNo
     * @param startDate
     * @param endDate
     * @return
     */
    public PagingResponse<InvoiceEntity> getAbnormal(Integer pageIndex, Integer pageSize, InvoiceTransTypeEnum transType, InvoiceBillTypeEnum billTypeEnum, InvoiceBillStatusEnum billStatusEnum, InvoiceCheckoutStatusEnum checkoutStatusEnum, String billNo, String startDate, String endDate, String transTypes) {
        String sql = "SELECT SQL_CALC_FOUND_ROWS i.*,s.name as contactName,info.specNumber,info.transTypeName,info.abnormalQuantity,info.billTime,g.name as goodsName FROM " + Tables.ErpInvoice + " as i LEFT JOIN " + Tables.ErpContact + " as s on s.id=i.contactId " +
                " LEFT JOIN  " + Tables.ErpInvoiceInfo + " as info on info.iid = i.id " +
                "LEFT JOIN " + Tables.ErpGoods + " g on g.id=info.goodsId WHERE i.isDelete=0  ";

        List<Object> params = new ArrayList<>();

        if (checkoutStatusEnum != null) {
            sql += " AND info.checkoutStatus=? ";
            params.add(checkoutStatusEnum.getIndex());
        }

        if (StringUtils.isEmpty(billNo) == false) {
            sql += " AND i.billNo=? ";
            params.add(billNo);
        } else {
            if (StringUtils.isEmpty(startDate) == false) {
                sql += " AND i.billDate >= ? ";
                params.add(startDate);

            }
            if (StringUtils.isEmpty(endDate) == false) {
                sql += " AND i.billDate <= ? ";
                params.add(endDate);
            }
            if (StringUtils.isEmpty(transTypes) == false) {
                sql += " AND i.transType = ? ";
                params.add(transTypes);
            }
        }

        sql += "ORDER BY i.id DESC LIMIT ?,?";
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        List<InvoiceEntity> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(InvoiceEntity.class), params.toArray(new Object[params.size()]));
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }

    /**
     * 获取退货单list
     *
     * @param pageIndex 页码
     * @param pageSize
     * @param number    单据编号
     * @param status    状态
     * @return
     */
    public PagingResponse<InvoiceEntity> getPurchaseCancelList(Integer pageIndex, Integer pageSize, InvoiceTransTypeEnum transType, String number, Integer status,Integer contactId) {
        String sql = "SELECT SQL_CALC_FOUND_ROWS i.*,c.name as contactName " + " FROM " + Tables.ErpInvoice +   " i " +
                " LEFT JOIN "+Tables.ErpContact+" as c on c.id=i.contactId "+
                " WHERE 1=1 ";

        List<Object> params = new ArrayList<>();
        if (StringUtils.isEmpty(number)==false) {
            sql += " AND i.billNo like ? ";
            params.add("%" + number + "%");
        }
        if (status >0) {
            sql += " AND i.status=? ";
            params.add(status);
        }
        if(null!=transType){
            sql += " AND i.transType=? ";
            params.add(transType.name());
        }else{
            sql += " AND (i.transType=? or i.transType=?) ";
            params.add(InvoiceTransTypeEnum.DaiFaRefund.getIndex());
            params.add(InvoiceTransTypeEnum.PUR_RETURN.getIndex());
        }
        if(contactId!=null && contactId>0){
            sql += " AND i.contactId=? ";
            params.add(contactId);
        }


        sql += " ORDER BY i.id DESC LIMIT ?,?";
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        List<InvoiceEntity> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(InvoiceEntity.class), params.toArray());
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }

    @Transactional
    public PagingResponse<InvoiceInfoListVo> getInvoiceInfoList(Integer pageIndex, Integer pageSize, InvoiceTransTypeEnum transType, String number, String billDate) {
        StringBuilder sql = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS ");
        sql.append(" i.billNo,i.billDate,i.billStatus,i.contactId");
        sql.append(" ,ii.amount,ii.quantity,ii.price,ii.inQuantity,ii.goodsNumber,ii.goodsId,ii.specId,ii.specNumber ");
        sql.append(",c.`name` AS contactName,CONCAT(spec.color_value,spec.size_value) AS specName,spec.color_image ");
        sql.append(" FROM erp_invoice_info AS ii ");
        sql.append(" LEFT JOIN erp_invoice AS i ON i.id=ii.iid ");
        sql.append(" LEFT JOIN erp_contact AS c ON c.id=i.contactId ");
        sql.append(" LEFT JOIN erp_goods_spec spec ON spec.id=ii.specId ");
        sql.append(" WHERE i.billStatus=1 ");

        List<Object> params = new ArrayList<>();
        sql.append(" AND i.billType = ? ");
        params.add(transType.getIndex());
        if (StringUtils.isEmpty(number)==false) {
            sql.append(" AND ii.specNumber LIKE ? ");
            params.add("%" + number + "%");
        }
        if(StringUtils.isEmpty(billDate)==false){
            sql.append(" AND i.billDate = ? ");
            params.add(billDate);
        }
        sql.append("  ORDER BY ii.id DESC LIMIT ?,? ");

        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        List<InvoiceInfoListVo> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(InvoiceInfoListVo.class), params.toArray());
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }


    /**
     * 根据iid查询退货单详情
     * @param iid
     * @return
     */
    public List<ErpInvoiceInfoVo> getErpInvoiceInfoByIid(Long iid){
        String erpInvoiceInfoSQL="select i.id,i.iid,i.goodsId,i.goodsNumber,i.specId,i.specNumber,i.quantity,i.inQuantity,i.status from "+Tables.ErpInvoiceInfo+" i where iid=?";
        var list= jdbcTemplate.query(erpInvoiceInfoSQL, new BeanPropertyRowMapper<>(ErpInvoiceInfoVo.class),iid);
        //查询仓位库存
        list.forEach(i->{
            String invoiceInfoStockSQL="select s.*,(select number from erp_stock_location  where id=s.locationId ) locationName from "+Tables.ErpGoodsStockInfo+" s where s.goodsId=? and s.specId=? and s.isDelete=0";
            i.setGoodStockList(jdbcTemplate.query(invoiceInfoStockSQL, new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class),i.getGoodsId(),i.getSpecId()));
        });
        return list;
    }

    /**
     * 采购退货商品出库
     * @param id 采购单id
     * @param ids 采购商品信息id组合
     * @param stockId 采购商品出库仓库id
     * @param number 采购商品出库数量
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> purchaseGoodOut(Integer id,String[] ids,String[] stockId,String[] number,Integer stockInUserId, String stockInUserName){
        try {
            /**1.查询退货单信息_erp_invoice**/
            var invoce= jdbcTemplate.queryForObject("select *  FROM " + Tables.ErpInvoice +  " where id=? AND billType='PUR_RETURN' ",new BeanPropertyRowMapper<>(InvoiceEntity.class),id);

            if(invoce.getChecked() != 1)
                return new ResultVo<>(EnumResultVo.Fail,"退货单状态为："+invoce.getChecked()+"，不能出库");
            if(invoce.getBillStatus().intValue() != 1)
                return new ResultVo<>(EnumResultVo.Fail,"退货单状态为："+invoce.getBillStatus()+"，不能出库");

//            if(invoce.getChecked().intValue()!=1)return new ResultVo<>(EnumResultVo.Fail,"采购单未审核不能出库");
//            if(invoce.getCheckoutStatus()==1)return new ResultVo<>(EnumResultVo.SUCCESS,"采购单已经出库");



            /*********添加出库数据*erp_stock_out_form*********/
            String stockOutFormSQL = "INSERT INTO "+Tables.ErpStockOutForm + " (stockOutNo,status,printStatus,printTime,createTime,createBy,modifyTime,completeTime,stockOutUserId,stockOutUserName,outType,sourceNo,sourceId) " +
                    " VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?)";
            //订单编号
//                jdbcTemplate.update(stockOutFormSQL,outNum,3,0,0,System.currentTimeMillis()/1000,stockInUserName,System.currentTimeMillis()/1000,System.currentTimeMillis()/1000,stockInUserId,stockInUserName);

            String outNum = "OUT"+OrderNumberUtils.getOrderIdByTime();
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(stockOutFormSQL, Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, outNum);
                    ps.setInt(2, 3);
                    ps.setInt(3, 0);
                    ps.setLong(4, 0);
                    ps.setLong(5, System.currentTimeMillis()/1000);
                    ps.setString(6, stockInUserName);
                    ps.setLong(7,System.currentTimeMillis()/1000);
                    ps.setLong(8, System.currentTimeMillis()/1000);
                    ps.setInt(9, stockInUserId);
                    ps.setString(10, stockInUserName);
                    ps.setInt(11, 2);//出库类型1订单拣货出库2采购退货出库
                    ps.setString(12, invoce.getBillNo());
                    ps.setLong(13,invoce.getId());
                    return ps;
                }
            }, keyHolder);

            Long outFormId = keyHolder.getKey().longValue();

            Integer inQuantity=0;
            for(int i=0,n=ids.length;i<n;i++){
                Integer num=Integer.parseInt(number[i]);
                inQuantity+=num;

                /**2.查询退货单商品信息信息_erp_invoice_info**/
                String invoiceInfoStockSQL="select * from "+Tables.ErpInvoiceInfo+"  where id=?";
                var invoiceInfo = jdbcTemplate.queryForObject(invoiceInfoStockSQL, new BeanPropertyRowMapper<>(InvoiceInfoEntity.class),ids[i]);

                /***********查询仓位库存，判断***********/
                var goodsStock = jdbcTemplate.queryForObject("SELECT gsi.*,sl.number as locationName FROM "+Tables.ErpGoodsStockInfo +" as gsi LEFT JOIN "+Tables.ErpStockLocation + " sl on sl.id=gsi.locationId WHERE gsi.specId=? AND gsi.locationId=? AND gsi.isDelete=0",new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class),invoiceInfo.getSpecId(),stockId[i]);
                if((goodsStock.getCurrentQty().longValue() - goodsStock.getLockedQty().longValue() )<num){
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                    return new ResultVo<>(EnumResultVo.DataError,goodsStock.getLocationName() +"仓位库存不足");
                }



                /*********添加出库数据*erp_stock_out_form_item*********/
                String stockOutFormItemSQL = "INSERT INTO erp_stock_out_form_item (formId,itemId,goodsId,specId,locationId,quantity,completeTime) value (?,?,?,?,?,?,?)";
                // jdbcTemplate.update(stockOutFormItemSQL,outFormId,ids[i],invoiceInfo.getGoodsId(),invoiceInfo.getSpecId(),stockId[i],number[i],System.currentTimeMillis() / 1000);
                String itemId = ids[i];
                String locationId = stockId[i];
                String quantity = number[i];
                KeyHolder stockOutFormItemKeyHolder = new GeneratedKeyHolder();
                jdbcTemplate.update(new PreparedStatementCreator() {
                    @Override
                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement ps = connection.prepareStatement(stockOutFormItemSQL, Statement.RETURN_GENERATED_KEYS);
                        ps.setLong(1, outFormId);
                        ps.setLong(2, Long.parseLong(itemId));
                        ps.setInt(3, invoiceInfo.getGoodsId());
                        ps.setInt(4, invoiceInfo.getSpecId());
                        ps.setInt(5, Integer.parseInt(locationId));
                        ps.setLong(6, Long.parseLong(quantity));
                        ps.setLong(7,System.currentTimeMillis()/1000);
                        return ps;
                    }
                }, stockOutFormItemKeyHolder);

                Long stockOutFormItemId = stockOutFormItemKeyHolder.getKey().longValue();

                /*********减批次库存 erp_goods_stock_info_item *********/
                //用库存信息goodsStock id 查出批次库存信息
                List<ErpGoodsStockInfoItemEntity> goodsStockInfoItem  =  jdbcTemplate.query("SELECT * FROM erp_goods_stock_info_item WHERE stockInfoId=? AND currentQty >0 order by id asc"
                ,new BeanPropertyRowMapper(ErpGoodsStockInfoItemEntity.class), goodsStock.getId());
                //先进先出 减少批次库存
                long hasOutQty = Long.parseLong(quantity);
                for (ErpGoodsStockInfoItemEntity tmp : goodsStockInfoItem) {
                    if(hasOutQty == 0 ) break;
                    if(tmp.getCurrentQty().longValue() >= hasOutQty){
                        //更新 erp_goods_stock_info_item 数据
                        jdbcTemplate.update("UPDATE erp_goods_stock_info_item SET currentQty=currentQty-? WHERE id=? ",hasOutQty,tmp.getId());
                        
                        //插入出库明细表批次出库记录erp_stock_out_form_item_detail
                        jdbcTemplate.update("INSERT INTO erp_stock_out_form_item_detail (stock_out_form_item_id,goods_stock_info_id,goods_stock_info_item_id,quantity) VALUE (?,?,?,?)"
                        ,stockOutFormItemId,tmp.getStockInfoId(),tmp.getId(),hasOutQty);

                        hasOutQty = 0;
                    }else{
                        //更新 erp_goods_stock_info_item 数据
                        jdbcTemplate.update("UPDATE erp_goods_stock_info_item SET currentQty=currentQty-? WHERE id=? ",tmp.getCurrentQty(),tmp.getId());
                        
                        //插入出库明细表批次出库记录erp_stock_out_form_item_detail
                        jdbcTemplate.update("INSERT INTO erp_stock_out_form_item_detail (stock_out_form_item_id,goods_stock_info_id,goods_stock_info_item_id,quantity) VALUE (?,?,?,?)"
                        ,stockOutFormItemId,tmp.getStockInfoId(),tmp.getId(),tmp.getCurrentQty());

                        hasOutQty -= tmp.getCurrentQty().longValue();
                    }
                }


                /********3.更新商品库存_erp_goods_spec***********/
                jdbcTemplate.update("UPDATE " + Tables.ErpGoodsSpec + " SET currentQty=currentQty-? WHERE id=?",number[i],invoiceInfo.getSpecId());

                /********4.更新商品库存_erp_goods_stock_info***********/
                String updErpGoodsStock = "UPDATE " + Tables.ErpGoodsStockInfo + " SET currentQty=currentQty-? WHERE goodsId=? and specId=? and locationId=? ";
                jdbcTemplate.update(updErpGoodsStock, number[i],invoiceInfo.getGoodsId(),invoiceInfo.getSpecId(),stockId[i]);

                /**5.更新库存商品信息—erp_invoice_info**/
                jdbcTemplate.update("update "+Tables.ErpInvoiceInfo+" set inQuantity=inQuantity+?,locationId=?,checkoutStatus=? where id=?"
                        ,number[i]
                        ,invoiceInfo.getQuantity()==(invoiceInfo.getInQuantity()+num) ? 1 : 0
                        ,stockId[i]
                        ,ids[i]);

                /**6.加入库存日志erp_goods_stock_logs**/
                String remark = "采购退货出库，退货单号：" + invoce.getBillNo();
                remark += " SKU：" + invoiceInfo.getSpecNumber();

                String logsSQL = "INSERT INTO " + Tables.ErpGoodsStockLogs + " (goodsId,goodsNumber,specId,specNumber,locationId,quantity,createTime,type,sourceType,sourceId,remark,createUserId,createUserName,createOn,currQty) VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                jdbcTemplate.update(logsSQL, invoiceInfo.getGoodsId(),invoiceInfo.getGoodsNumber(),invoiceInfo.getSpecId(),invoiceInfo.getSpecNumber(),stockId[i],number[i],
                        new Date(), EnumGoodsStockLogType.OUT.getIndex(), EnumGoodsStockLogSourceType.PUR_RETURN.getIndex(),invoiceInfo.getId(), remark,stockInUserId,stockInUserName
                        ,System.currentTimeMillis() / 1000,(goodsStock.getCurrentQty() - Long.parseLong(number[i])));
            }
            /**7.更新退货单出库信息**/
            if(invoce.getTotalQuantity()==(invoce.getInQuantity()+inQuantity)){
                //全部出库，更新状态为已出库
                jdbcTemplate.update("update " + Tables.ErpInvoice +  " set inQuantity=inQuantity+?,checkoutStatus=?,billStatus=? where id=?",inQuantity, 1 , InvoiceBillStatusEnum.Out.getIndex(),id);
            }else {
                //没有全部出库
                jdbcTemplate.update("update " + Tables.ErpInvoice + " set inQuantity=inQuantity+?,checkoutStatus=0 where id=?", inQuantity, id);
            }


            //TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.SUCCESS);
        }catch (Exception e){
//            System.out.println(e);
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.SystemException,"系统异常."+e.getMessage());
        }
    }

    /**
     * 查询退货打印商品信息
     * @param id
     * @return
     */
    public ErpInvoiceVo getDetailById(Long id){
        try {
            var invoice = jdbcTemplate.queryForObject("select *  FROM " + Tables.ErpInvoice +  " where id=?",new BeanPropertyRowMapper<>(ErpInvoiceVo.class),id);

            invoice.setInvoiceInfoList(jdbcTemplate.query("select ii.*,sl.number as locationName from "+Tables.ErpInvoiceInfo+" as ii LEFT JOIN "+Tables.ErpStockLocation+" as sl on sl.id=ii.locationId  where ii.iid=?"
                    ,new BeanPropertyRowMapper<>(InvoiceInfoEntity.class),id));

            return invoice;
        }catch (Exception e){
            return null;
        }

    }

    /**
     * 采购订单审核
     * @param erpInvoiceId
     * @param status
     * @return
     */
    public void checkErpInvoice(Integer erpInvoiceId,Integer status){
        jdbcTemplate.update("update erp_invoice set billStatus=?,checked=? where id=?",status,status,erpInvoiceId);
        jdbcTemplate.update("update erp_invoice_info set status=? where iid=?",status,erpInvoiceId);
    }

    /**
     * 单据审核
     * @param InvoiceId 单据id
     * @param checked 0 未审核 1通过 3:拒绝
     * @param checkName 审核人
     * @return
     */
    public Integer checkInvoice(Integer InvoiceId,Integer checked,String checkName){
//        return jdbcTemplate.update("update erp_invoice set checked=?,checkName=? where id=?",checked,checkName,InvoiceId);
        jdbcTemplate.update("update erp_invoice set billStatus=?,checked=?,checkName=? where id=?",checked,checked,checkName,InvoiceId);
        jdbcTemplate.update("update erp_invoice_info set status=? where iid=?",checked,InvoiceId);
        return 1;
    }

    /**
     * 删除采购商品列表
     * @param erpInvoiceInfoId
     * @return
     */
    public ResultVo<Integer> delErpInvoiceInfo(Long erpInvoiceInfoId,Long erpInvoiceId){
        try {
            jdbcTemplate.update("delete from erp_invoice_info where id=?",erpInvoiceInfoId);
            double totalAmount=0d;
            Integer totalQuantity=0;
            var infoList = jdbcTemplate.query("select * from erp_invoice_info where iid=?",new BeanPropertyRowMapper<>(ErpInvoiceInfoVo.class),erpInvoiceId);
            for(var info : infoList){
                totalAmount+=info.getPrice()*info.getQuantity();
                totalQuantity+=info.getQuantity().intValue();
            }
            jdbcTemplate.update("update erp_invoice set totalAmount=?,totalQuantity=?,arrears=totalAmount-rpAmount where id=?",totalAmount,totalQuantity,erpInvoiceId);
            return new ResultVo<>(EnumResultVo.SUCCESS,"成功");
        }catch (Exception ex){
            return new ResultVo<>(EnumResultVo.Fail,"异常："+ex);
        }
    }

    /**
     * 修改采购单商品规格信息
     * @param InvoiceInfoId
     * @param price
     * @param quantity
     */
    public void updInvoiceInfo(Integer erpInvoiceId,Integer InvoiceInfoId,Double price,Integer quantity){
        if(price>-1){
            jdbcTemplate.update("update erp_invoice_info set price=? where id=?",price,InvoiceInfoId);
        }
        if(quantity.intValue()>-1){
            jdbcTemplate.update("update erp_invoice_info set quantity=? where id=?",quantity,InvoiceInfoId);
        }
        double totalAmount=0d;
        Integer totalQuantity=0;
        var infoList = jdbcTemplate.query("select * from erp_invoice_info where iid=?",new BeanPropertyRowMapper<>(ErpInvoiceInfoVo.class),erpInvoiceId);
        for(var info : infoList){
            totalAmount+=info.getPrice()*info.getQuantity();
            totalQuantity+=info.getQuantity().intValue();
        }
        jdbcTemplate.update("update erp_invoice set totalAmount=?,totalQuantity=?,arrears=totalAmount-rpAmount,amount=? where id=?",totalAmount,totalQuantity,totalAmount,erpInvoiceId);
    }

    /**
     * 采购单商品添加
     * @param id
     * @param invoiceInfo
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> addInvoiceItem(Long id, ErpPurchaseGoodVo invoiceInfo){
        InvoiceEntity purchase=null;
        try {
            purchase = jdbcTemplate.queryForObject("select * from erp_invoice where id=?",new BeanPropertyRowMapper<>(InvoiceEntity.class),id);
            if(purchase.getChecked().intValue()==1)return new ResultVo<>(EnumResultVo.ParamsError,"采购单已审核不能修改");
        }catch (Exception e){
            return new ResultVo<>(EnumResultVo.ParamsError,"采购单不存在");
        }
        try {
            if(invoiceInfo.getErpInvoiceInfoId().intValue()==0){



                StringBuilder addErpInvoiceInfo=new StringBuilder("insert erp_invoice_info set ");
                addErpInvoiceInfo.append(" iid=?,");
                addErpInvoiceInfo.append(" billNo=?,");
                addErpInvoiceInfo.append(" transType=?,");
                addErpInvoiceInfo.append(" transTypeName=?,");
                addErpInvoiceInfo.append(" amount=?,");
                addErpInvoiceInfo.append(" billDate=?,");
                addErpInvoiceInfo.append(" goodsId=?,");
                addErpInvoiceInfo.append(" goodsNumber=?,");
                addErpInvoiceInfo.append(" specId=?,");
                addErpInvoiceInfo.append(" specNumber =?,");
                addErpInvoiceInfo.append(" price=?,");
                addErpInvoiceInfo.append(" status=?,");
                addErpInvoiceInfo.append(" quantity=?");
                jdbcTemplate.update(addErpInvoiceInfo.toString(),
                        purchase.getId(),
                        purchase.getBillNo(),
                        purchase.getTransType(),
                        purchase.getTransTypeName(),
                        invoiceInfo.getAmount(),
                        purchase.getBillDate(),
                        invoiceInfo.getGoodsId(),
                        invoiceInfo.getGoodsNumber(),
                        invoiceInfo.getSpecId(),
                        invoiceInfo.getSpecNumber(),
                        invoiceInfo.getPrice(),
                        purchase.getBillStatus(),
                        invoiceInfo.getQuantity());
            }
            double totalAmount=0d;
            Integer totalQuantity=0;
            var infoList = jdbcTemplate.query("select * from erp_invoice_info where iid=?",new BeanPropertyRowMapper<>(ErpInvoiceInfoVo.class),id);
            for(var info : infoList){
                totalAmount+=info.getPrice()*info.getQuantity();
                totalQuantity+=info.getQuantity().intValue();
            }
            jdbcTemplate.update("update erp_invoice set totalAmount=?,totalQuantity=?,arrears=totalAmount-rpAmount,amount=? where id=?",totalAmount,totalQuantity,totalAmount,purchase.getId());
            return new ResultVo<>(EnumResultVo.SUCCESS,"成功");
        }catch (Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.ParamsError,"系统异常");
        }
    }

    /**
     * 采购入库明细
     * @param startDate
     * @param endDate
     * @return
     */
    public List<ErpStockInFormItemVo> invoiceItemPOList (String startDate,String endDate){
        StringBuilder sb =new StringBuilder("SELECT inv.billNo,inv.contractNo,f.stockInTime1,fi.quantity,invi.price as purPrice, g.name as goodsName,g.number as goodsNumber,gs.specNumber,gs.color_value,gs.size_value,fi.specId  \n" +
                "FROM  erp_stock_in_form_item   fi \t LEFT JOIN  erp_stock_in_form  f on f.id = fi.formId \t LEFT JOIN  erp_invoice   inv on inv.id = f.invoiceId \t LEFT JOIN  erp_invoice_info   invi on invi.id = fi.itemId \t\n" +
                "LEFT JOIN  erp_goods  g on g.id = fi.goodsId \t \n" +
                "LEFT JOIN  erp_goods_spec  gs on gs.id = fi.specId");
        sb.append(" WHERE f.inType=1  and f.stockInTime1 >0 ");
        List<Object> params = new ArrayList<>();
        if(!StringUtils.isEmpty(startDate)) {
            //按一级大类查询
            sb.append(" AND FROM_UNIXTIME(f.stockInTime1,'%Y-%m-%d') > ? ");
            params.add(startDate);
        }

        if(!StringUtils.isEmpty(endDate)) {
            //按一级大类查询
            sb.append(" AND FROM_UNIXTIME(f.stockInTime1,'%Y-%m-%d') <= ?");
            params.add(endDate);
        }
        var list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpStockInFormItemVo.class), params.toArray(new Object[params.size()]));
        return list;
    }


    /**
     * 付款2022-4-21
     * @param id
     * @param amount
     * @return
     */
    @Transactional
    public ResultVo<Integer> purchasePayAmount(Long id, Double amount) {
        try {
            var pur = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpInvoice + " WHERE id=?", new BeanPropertyRowMapper<>(InvoiceEntity.class), id);
            if(pur.getChecked().intValue() != 1)  return new ResultVo<>(EnumResultVo.DataError,"表单状态不对"+pur.getChecked());
            if(pur.getHxStateCode().intValue() == 2)  return new ResultVo<>(EnumResultVo.DataError,"已经付过款了");
            // if(pur.getTotalAmount().doubleValue() - pur.getRpAmount().doubleValue() < amount)
            //     return new ResultVo<>(EnumResultVo.DataError,"支付金额不正确");

            //更新付款金额、状态
            jdbcTemplate.update("UPDATE "+ Tables.ErpInvoice+" SET rpAmount=rpAmount+?,hxStateCode=2,hxAmount=?,modifyTime=? WHERE id=?"
                    ,amount,amount,System.currentTimeMillis()/1000,id);

            //添加到资金明细 erp_funds_detail
            String sql = "INSERT INTO erp_funds_detail (`type`,source,amount,sourceNo,remark,createDate,billDate) VALUE (1,'PURCHASE',?,?,?,?,?)";
            String remark= "采购单款项支付，采购单ID："+id+"，采购单号："+pur.getBillNo()+"采购单总金额："+pur.getTotalAmount()+"采购单日期："+pur.getBillDate();
            jdbcTemplate.update(sql,amount,pur.getBillNo(),remark,new Date(),pur.getBillDate());

            return new ResultVo<>(EnumResultVo.SUCCESS,"SUCCESS");
        }catch (Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.ParamsError,"系统异常");
        }
    }

    @Transactional
    public ResultVo<Integer> purchaseRefundAmount(Long id, Double amount) {
        try {
            var pur = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpInvoice + " WHERE id=?", new BeanPropertyRowMapper<>(InvoiceEntity.class), id);
            if(pur.getChecked().intValue() != 1)  return new ResultVo<>(EnumResultVo.DataError,"表单状态不对"+pur.getChecked());
            if(pur.getHxStateCode().intValue() == 2)  return new ResultVo<>(EnumResultVo.DataError,"已经退过款了");
            if(pur.getTotalAmount().doubleValue() - pur.getRpAmount().doubleValue() < amount)
                return new ResultVo<>(EnumResultVo.DataError,"支付金额不正确");

            //更新付款金额、状态
            jdbcTemplate.update("UPDATE "+ Tables.ErpInvoice+" SET rpAmount=rpAmount+?,hxStateCode=2,hxAmount=?,modifyTime=? WHERE id=?"
                    ,amount,amount,System.currentTimeMillis()/1000,id);

            //添加到资金明细 erp_funds_detail
            String sql = "INSERT INTO erp_funds_detail (`type`,source,amount,sourceNo,remark,createDate,billDate) VALUE (2,'PURREFUND',?,?,?,?,?)";
            String remark= "采购退货退款，退货单ID："+id+"，退货单号："+pur.getBillNo()+"退货总金额："+pur.getTotalAmount()+"退货单日期："+pur.getBillDate();
            jdbcTemplate.update(sql,amount,pur.getBillNo(),remark,new Date(),pur.getBillDate());

            return new ResultVo<>(EnumResultVo.SUCCESS,"SUCCESS");
        }catch (Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.ParamsError,"系统异常");
        }
    }

}
